/* eslint-disable no-plusplus */
const eventClass = require('./../events/eventClass');

const Excel = eval('require')('exceljs/modern.nodejs');
const fs = eval('require')('fs');

class excelClass extends eventClass {
    constructor() {
        super('excel');
        this.csvTopChar = String.fromCharCode(65279);
    }

    /**
     * results = [
     *      {
     *          name: 'sheetName',
     *          header: [
     *              { header: '用户名', key: 'userName', width: 25 },
     *              { header: '方法名', key: 'methodName', width: 25 },
     *              { header: '到期时间', key: 'expiredDate', width: 25 }
     *          ],
     *          data: [
     *              {
     *                  userName: 'ibas',
     *                  methodName: 'method',
     *                  expireDate: '2019-01-01'
     *              }
     *          ]
     *      }
     * ]
     * */
    writeExcel(cb, { results, filename, rowHeight }) {
        const workbook = new Excel.Workbook();
        const $this = this;
        results.map((res, ind) => {
            const sheet = workbook.addWorksheet(res.name || (`sheet${ind}`), {
                properties: {
                    defaultRowHeight: rowHeight || 15,
                },
            });
            if (res.header) {
                sheet.columns = res.header;
            }
            sheet.addRows(res.data);
        });
        workbook.xlsx.writeFile(filename)
            .then(() => {
                $this.returnMethod(cb, {});
            })
            .catch(err => $this.returnMethod(cb, { err }));
    }

    /**
     * 保持数据到 csv
     * result    [ ['a','b','c'],['d','e','f'] ]
     * filename
     */
    writeCSV(cb, { results, filename }) {
        fs.writeFileSync(filename, this.csvTopChar + results.map(_ => _.join(',')).join('\n'));
        this.returnMethod(cb, {});
    }

    /**
     * @param cb
     * @param filename
     * @param header    [ [ 第一个 sheet 中的头部信息 ], [ 'id','name','age' ] ]
     */
    readExcel(cb, { filename, header }) {
        const workbook = new Excel.Workbook();
        const dearRow = (row, ind, header) => {
            row = row.slice(1, row.length);
            if (ind - 1) {
                const obj = {};
                row.forEach((r, index) => {
                    obj[header[index] || (`undefined_${index}`)] = r;
                });
                return obj;
            }
            return row.map((_, index) => {
                return {
                    header: _,
                    key: header[index] || (`undefined_${index}`),
                };
            });
        };
        header = header instanceof Array ? header : [header];
        workbook.xlsx.readFile(filename)
            .then(() => {
                const data = [];
                for (let i = 1; i < workbook._worksheets.length; i++) {
                    const sheet = {
                        name: workbook._worksheets[i].name,
                        data: [],
                        header: [],
                    };
                    let h = header[i - 1];
                    h = h instanceof Array ? h : null;
                    workbook._worksheets[i].eachRow((row, ind) => {
                        if (h) {
                            const ret = dearRow(row.values, ind, h);
                            if (ret instanceof Array) {
                                sheet.header = ret;
                            } else {
                                sheet.data.push(ret);
                            }
                        } else {
                            sheet.data.push(row.values);
                        }
                    });
                    data.push(sheet);
                }
                this.returnMethod(cb, { data });
            })
            .catch((err) => {
                this.returnMethod(cb, { err });
            });
    }

    readCSV(cb, { filename }) {
        let ret = fs.readFileSync(filename, 'utf-8');
        if (ret[0] === this.csvTopChar) {
            ret = ret.substring(1);
        }
        this.returnMethod(cb, {
            data: ret.split('\n').map(_ => _.split(',')),
        });
    }

    dbToExcel(cb, { sql, filename, keyLabel, sheetName }) {
        const $this = this;
        this.events.mysql.checkConnection((err) => {
            if (err) {
                this.returnMethod(cb, { err });
            } else {
                $this.events.mysql.query((qerr, qdata) => {
                    if (qerr) {
                        $this.returnMethod(cb, { qerr });
                    } else if (qdata.length) {
                        const header = [];
                        keyLabel = keyLabel || {};
                        for (const i in qdata[0]) {
                            header.push({
                                header: keyLabel[i] || i,
                                key: i,
                            });
                        }
                        $this.writeExcel(cb, {
                            results: [
                                {
                                    header,
                                    data: qdata,
                                    sheetName,
                                },
                            ],
                            filename,
                        });
                    } else {
                        $this.returnMethod(cb, { err: '数据条目为 0' });
                    }
                }, { sql });
            }
        });
    }
}

module.exports = excelClass;
